package com.cms.biz.pending.query;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import net.sf.json.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.stereotype.Repository;

import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.builder.base.data.ESPRowSet;
import com.efounder.eai.data.JParamObject;
import com.efounder.eai.data.JResponseObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.enums.EMPQueryParamEnum;
import com.mrp.persistence.queryAnalyse.pageQuery.bean.EMPPageQuery;
import com.pub.util.EMPLimitConvertUtil;
import com.pub.util.StringFunction;

import dwz.common.util.DateUtil;


/**
 * <p>Title: 项目查询分析</p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2010</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
@Repository
public class EMPPendingDepartmentComfireQuery {

	@Autowired
	private JdbcTemplate                  jdbcTemplate;

	public JResponseObject QueryObject(JParamObject PO){
		JConnection         conn = null;
		JStatement          stmt = null;
		EFDataSet   queryDataset = null;
		JResponseObject       RO = null;
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			conn.setAutoCommit(true);
			stmt = conn.createStatement();
	
			// 获取查询数据
			queryDataset = getFlowDataSet(stmt, PO);
			
			//判断到期日
			convertDataSet(queryDataset);
			
			// 封装返回数据
		   	RO = new JResponseObject(queryDataset, 0);
		} catch(Exception ce) {
			ce.printStackTrace();
			RO = new JResponseObject(null, -1, ce);
		} finally {
			EMPSQLUtil.closeAllResources(null, stmt, conn);
		}
		return RO;
	}
	
	/**
	 * 根据流程节点ID获取流程数据
	 * @throws Exception
	 */
	public EFDataSet getFlowDataSet(JStatement stmt, JParamObject PO) throws Exception {
		String                     strSql = "";
		String                    USER_ID = PO.GetValueByParamName("USER_ID", "");
		String                 USER_ORGID = PO.GetValueByParamName("USER_ORGID", "");
		ResultSet                      rs = null;
		EFDataSet               objColsDS = null;
		String                      F_DQR = "0";
		EFDataSet             flowDataSet = EFDataSet.getInstance("FLOW_TASK_LIST");
		
		strSql = "select FLOW_ID, NODE_NAME as FLOW_MC, NODE_ID, '' as BIZ_GUID, " 
		       + " '' as OP_ID, '' as BIZ_DJBH, '' as BIZ_KJQJ, '' as OP_NOTE, '' as OP_USER_NAME, '' as F_TJRQ" 
		       + " from FLOW_NODE_LIST where FLOW_ID = 'PSHT_EDIT_FLOW' and NODE_ID = 'N4' and " 
			   + " exists(SELECT 1 FROM FLOW_USER_LIST where FLOW_USER_LIST.FLOW_BH = FLOW_NODE_LIST.FLOW_ID and ((FLOW_USER_LIST.FLOW_BM = '"
			   + USER_ORGID
			   + "' and FLOW_USER_LIST.F_TYPE = 'BM')or (FLOW_USER_LIST.FLOW_USER = '"
			   + USER_ID
			   + "' and FLOW_USER_LIST.FLOW_BM = '" + USER_ORGID + "' and FLOW_USER_LIST.F_TYPE = 'USER'))"
	       	   + " and FLOW_USER_LIST.NODE_BH = FLOW_NODE_LIST.NODE_ID)"
			   + " order by NODE_ID";
		rs = stmt.executeQuery(strSql);
		
		if(rs != null && rs.next()) {
			strSql = " select * from SYS_OBJCOLS" + " where OBJ_ID = 'FLOW_TASK_LIST' and F_STAU = '1'";
			rs = stmt.executeQuery(strSql);
			objColsDS = DataSetUtils.resultSet2DataSet(rs, objColsDS);
			
			strSql = " select * from SYS_MDL_VAL where MDL_ID = 'PSHTModel' and MDL_KEY = 'F_DQR'";
			rs = stmt.executeQuery(strSql);
			
			if(rs != null && rs.next()) {
				F_DQR = rs.getString("MDL_VAL");
			}
			
			strSql = " select PSHTMX.*, '" + F_DQR + "' as F_DQR from PSHTMX where exists (select 1 from FLOW_TASK_LIST where FLOW_TASK_LIST.FLOW_ID = 'PSHT_EDIT_FLOW' and "
				    + " FLOW_TASK_LIST.TASK_STATUS = 'processing' and FLOW_TASK_LIST.RESR_STATUS = 'pending' and FLOW_TASK_LIST.NODE_TAG = 'N4' and " 
				    + "(FLOW_TASK_LIST.TASK_TO_UNIT IS NULL or FLOW_TASK_LIST.TASK_TO_UNIT = '' or FLOW_TASK_LIST.TASK_TO_UNIT = ' ' or FLOW_TASK_LIST.TASK_TO_UNIT = '" + USER_ORGID + "') and "
				    + "(FLOW_TASK_LIST.TASK_TO_USER IS NULL or FLOW_TASK_LIST.TASK_TO_USER = '' or FLOW_TASK_LIST.TASK_TO_USER = ' ' or FLOW_TASK_LIST.TASK_TO_USER = '" + USER_ID + "') "
				    + " and FLOW_TASK_LIST.BIZ_DJBH = PSHTMX.F_DJBH and FLOW_TASK_LIST.BIZ_GUID = PSHTMX.F_GUID and FLOW_TASK_LIST.BIZ_DATE = PSHTMX.F_KJND) and PSHTMX.F_BMBH = '" + USER_ORGID 
				    + "' order by F_KJND, F_DJBH";
			rs = stmt.executeQuery(strSql);
			flowDataSet = DataSetUtils.resultSet2DataSet(rs, flowDataSet);
		}
		
		return flowDataSet;
	}
	
	/**
	 * 获取字典元数据
	 * @param conn
	 * @param tableName
	 * @return
	 */
	public EFRowSet getDictRow(JStatement stmt, String tableName) {
		JConnection    conn = null;
		ResultSet        rs = null;
		EFDataSet    dictDS = EFDataSet.getInstance("SYS_DICTS");
		EFRowSet     dictRS = null;
		String       strSql = " select * from SYS_DICTS where DCT_ID = '" + tableName + "'";
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();
			rs = stmt.executeQuery(strSql);
			dictDS = DataSetUtils.resultSet2DataSet(rs, dictDS);
			if(dictDS != null && dictDS.getRowCount() > 0) {
				dictRS = dictDS.getRowSet(0);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return dictRS;
	}
	
	public void convertDataSet(EFDataSet queryDataSet) {
		EFRowSet flowItemRowSet = null;
		String curDate = DateUtil.date3String("yyyyMMdd");
		String F_JZRQ  = "";
		String   F_DQR = "";
		int       iDQR = 0;
		String  F_QRRQ = "";
		String XG_JZRQ = "";
		String endDate = "";
		int betweenDay = 0;
		
		for(int i = 0; i < queryDataSet.getRowCount(); i++) {
			flowItemRowSet = queryDataSet.getRowSet(i);
			F_DQR = flowItemRowSet.getString("F_DQR", "0");
			try {
				iDQR = Integer.parseInt(F_DQR);
			} catch(Exception ce) {
				iDQR = 0;
			}
			if(flowItemRowSet.getDate("F_JZRQ", null) != null) {
				F_JZRQ = flowItemRowSet.getDate("F_JZRQ", null).toString().replaceAll("-", "");
			}
			
			if(flowItemRowSet.getString("XG_JZRQ", null) != null) {
				XG_JZRQ = flowItemRowSet.getString("XG_JZRQ", null).toString().replaceAll("-", "");
			}
			if(flowItemRowSet.getDate("F_QRRQ", null) != null) {
				F_QRRQ = flowItemRowSet.getDate("F_QRRQ", null).toString().replaceAll("-", "");
			}
			endDate = (XG_JZRQ.trim().equals("") ? F_JZRQ : XG_JZRQ);
			if(curDate.compareTo(endDate) <= 0) {	
				if(F_QRRQ.trim().equals("")) {
					flowItemRowSet.putString("F_QRZT", "0");
				} else {
					flowItemRowSet.putString("F_QRZT", "1");
				}
				
				betweenDay = com.common.util.DateUtil.daysBetween(curDate, "yyyyMMdd", endDate, "yyyyMMdd");	
				if(iDQR > 0) {
					if(iDQR > betweenDay) {
						flowItemRowSet.putString("F_YJZT", "1");
					}
				}
			} else {
				flowItemRowSet.putString("F_QRZT", "2");
				flowItemRowSet.putString("F_YJZT", "2");
				betweenDay = com.common.util.DateUtil.daysBetween(endDate, "yyyyMMdd", curDate, "yyyyMMdd");	
			}
			flowItemRowSet.putNumber("F_XCTS", betweenDay);
		}
	}
}
